<?php

namespace app\common\controller;

use app\BaseController;
use app\common\service\MysqlBackup;
use app\model\BackupsModel;
use app\model\UserModel;
use think\App;
use think\facade\Config;
use think\facade\Db;
use think\response\Json;

class Backups extends BaseController
{
    protected $mode;
    protected $backup;
    protected $rootPath;

    public function __construct(App $app)
    {
        parent::__construct($app);
        $config = Config::get("database");
        $this->backup = new \org\Baksql($config['connections']['mysql']);
        $this->mode = new BackupsModel();
        $rootPaths = $_SERVER['DOCUMENT_ROOT'];
        $this->rootPath = str_replace('\\', '/', $rootPaths).'/public'; // 文档根目录
    }

    public function addBackups(): bool|string
    {
        try {
            $param = $this->request->param();
            $this->validate($param,[
                'name|名称' => 'require'
            ]);
            $backupService = new MysqlBackup();
            // 可选配置：根据需求调整
            $partSize = input('part_size', 100, 'intval'); // 分卷大小（M）
            $ignoreTables = input('ignore_tables', '', 'trim'); // 额外忽略的表，逗号分隔
            $compress = input('compress', 1, 'intval') === 1; // 是否压缩

            // 设置配置
            $backupService->setPartSize($partSize);
            if ($ignoreTables) {
                $backupService->setIgnoreTables(explode(',', $ignoreTables));
            }
            if (!$compress) {
                $backupService->disableCompress();
            }

            $result = $backupService->backup();
            if (!$result['status']) throw new \Exception($result['msg']);
            // 返回文件下载地址
            $this->mode->insert([
                'unit_id'=>$this->userData['unit_id'],
                'company_id'=>$this->userData['company_id'],
                'areas_id'=>$this->userData['areas_id'],
                'uid'=>$this->userData['id'],
                'name'=>$param['name'],
                'path'=>$result['data']['path'],
                'size'=>round($result['data']['size']/(1024*1024),2),
                'time'=>time(),
            ]);
            return success($result['data']['path']);
        } catch (\Exception $e) {
            return error($e->getMessage(), $e->getCode(),[
                'line' => $e->getLine(),
                'file' => $e->getFile(),
            ]);
        }
    }

    /**
     * 创建备份
     * @return bool|string
     */
    public function addBackups2(): bool|string
    {
        $uid = $this->userData['id'];
        try {
            $name = $this->request->param('name');
            $unit_id = $this->userData['unit_id'];
            $company_id = $this->userData['company_id'];
            $areas_id =  $this->userData['areas_id'];
            if(empty($name)) throw new \Exception('缺少参数！');
            $tables = $this->getAllTables();
            //去掉不要备份的表
            $tempKey = ['areas','auth_rule','auth_rule_type','job_fail_log','tag','user_archive_option','user_archive_template','user_archive_type',
                'platform_auth_rule','company'];
            foreach($tempKey as &$v){
                $v = env('DB_PREFIX').$v;
            }
            foreach($tables as $key=>$item){
                if(in_array($item,$tempKey)){
                    unset($tables[$key]);
                }
            }
//            $tables[0] = 'jkyl_backups';
            $file = $this->backup->backup(array_values($tables),$name);//导入sql文本完成
            $file2 = substr($file, 1, strlen($file) - 1);
            if (file_exists($this->rootPath.$file2)) {
                $size = filesize($this->rootPath.$file2.$name.'.sql'); // 获取文件大小
                $size = $size/(1024*1024);
            } else {
                throw new \Exception('文件不存在或无法访问！');
            }
            // 返回文件下载地址
            $insert = [
                'unit_id'=>$unit_id,
                'company_id'=>$company_id,
                'areas_id'=>$areas_id,
                'name'=>$name,
                'uid'=>$uid,
                'time'=>time(),
                'path'=>$file2.$name.'.sql',
                'size'=>round($size,2),
            ];
            $this->mode->insert($insert);
            return success($file2.$name.'.sql');
        } catch (\Exception $e) {
            return error($e->getMessage(), $e->getCode(),[
                'line' => $e->getLine(),
                'file' => $e->getFile(),
            ]);
        }
    }

    /**
     * @param $dbName
     * @param $name    //备份文件的名称
     * @return string
     */
    function exportDatabase($dbName,$name): string
    {
        $tables = $this->getAllTables();
        //去掉不要备份的表
        $tempKey = ['areas','auth_rule','auth_rule_type','job_fail_log','tag','user_archive_option','user_archive_template','user_archive_type',
            'platform_auth_rule','company'];
        foreach($tempKey as &$v){
            $v = env('DB_PREFIX').$v;
        }
        foreach($tables as $key=>$item){
            if(in_array($item['Tables_in_jkyl'],$tempKey)){
                unset($tables[$key]);
            }
        }
        $path = './export/Backups/'.date('Ymd').'/';
        if (!is_dir($path)) {
            mkdir($path, 0755, true);
        }
        $sqlFile = $path.$name.'.sql';
        $handle = fopen($sqlFile, 'w');
        foreach ($tables as $table) {
            $tableName = $table['Tables_in_' . $dbName];
            // 获取表结构
            $structure = $this->getTableStructure($tableName);
            if (stripos($structure, 'CREATE') === 0){
                //删除表
                $tempSql = /** @lang text */
                    "DROP TABLE IF EXISTS `".$tableName."`";
                fwrite($handle, $tempSql . ";\n");
//                fwrite($handle, $tempSql . ";\n\n");
            }
            fwrite($handle, $structure . ";\n\n");
            // 获取表数据
            $data = $this->getTableData($tableName);
            foreach ($data as $row) {
                $values = array_map(function($value) {
                    if($value === NULL){
                        $value = NUll;
                    }
                    if(is_string($value)){
                        return "'" . addslashes($value) . "'";
                    }else{
                        return $value;
                    }
                }, $row);
                $tempStr = '';
                foreach ($values as $item){
                    if($item == Null){
                        $jsonOutput = str_replace('null', 'NULL', json_encode($item));
                        $tempStr .= $jsonOutput.',';
                    }else{
                        $tempStr .= $item.',';
                    }
                }
                $insertSQL = /** @lang text */
                    "INSERT INTO `".$tableName ."`VALUES (" . trim($tempStr,',') . ");\n";
                fwrite($handle, $insertSQL);
            }
            fwrite($handle, "\n\n");
        }
        fclose($handle);
        return $sqlFile;
    }

    /**
     * 获取所有表名
     * @return mixed
     */
    function getAllTables(): mixed
    {
        $sql = 'SHOW TABLES';
        $list = Db::query($sql);
        $tables = array();
        foreach ($list as $value) {
            $tables[] = $value['Tables_in_jkyl'];
        }
        return $tables;
//        return Db::query("SHOW TABLES");
    }

    /**
     * 获取表结构
     * @param $tableName
     * @return mixed
     */
    function getTableStructure($tableName): mixed
    {
        $result = Db::query("SHOW CREATE TABLE $tableName");
        return $result[0]['Create Table'];
    }

    /**
     * 获取表数据
     * @param $tableName
     * @return array
     */
    function getTableData($tableName): array
    {
        return Db::table($tableName)->select()->toArray();
    }

    /**
     * 删除备份
     * @return bool|string
     */
    public function delBackups(): bool|string
    {
        try {
            $id = $this->request->param('id');
            if(empty($id)) throw new \Exception('缺少参数！');
            $ids = explode(',',$id);
            $this->mode->where('id', 'in', $ids)->update([
                'is_del' => 1,
                'del_time' => time()
            ]);
            return success([], '删除成功！');
        } catch (\Exception $e) {
            return error($e->getMessage(), $e->getCode(),[
                'line' => $e->getLine(),
                'file' => $e->getFile(),
            ]);
        }
    }

    /**
     * 导入备份
     * @return bool|string
     */
    public function importBackups(): bool|string
    {
        try {
            $param = $this->request->param();
            $this->validate($param,[
                'path|文件地址' => 'require'
            ]);
            $pathInfo = pathinfo($param['path']);
            if ($pathInfo['extension'] != 'sql') throw new \Exception('仅支持sql文件！');
            $insert = [
                'company_id'=>$this->userData['company_id'],
                'areas_id'=>$this->userData['areas_id'],
                'unit_id'=>$this->userData['unit_id'],
                'name'=>basename($param['path']),
                'uid'=>$this->userData['id'],
                'path'=>$param['path'],
                'size'=>round(fileSize($param['path'])/(1024*1024),2),
                'time'=>time(),
            ];
            $this->mode->insert($insert);
            return success([], '导入成功！');
        } catch (\Exception $e) {
            return error($e->getMessage(), $e->getCode(),[
                'line' => $e->getLine(),
                'file' => $e->getFile(),
            ]);
        }
    }
    public function importBackups2(): bool|string
    {
        try {
            $uid = $this->userData['id'];
            $unit_id = $this->userData['unit_id'];
            $company_id = $this->userData['company_id'];
            $areas_id =  $this->userData['areas_id'];
            if(empty($_FILES['file'])) throw new \Exception('请选择文件') ;
            $file = $_FILES['file'];
//            $file = $this->request->file('file');
            $fileName = $file['name'];
            $size = ($file['size'])/(1024*1024);
            $upload = new \app\common\Upload();
            // 设置上传类型和文件大小
            $upload = $upload->local()->size(100);
            $file_name = '/export/Backups/'.date('Ymd').'/'.(str_replace('.sql','',$fileName));
            $ext = '.sql';
            $upload->upload($file,'file',$ext,$file_name);
            $insert = [
                'company_id'=>$company_id,
                'areas_id'=>$areas_id,
                'unit_id'=>$unit_id,
                'name'=>$fileName,
                'uid'=>$uid,
                'time'=>time(),
                'path'=>$file_name.$ext,
                'size'=>round($size,2),
            ];
            $this->mode->insert($insert);
            return success([], '导入成功！');
        } catch (\Exception $e) {
            return error($e->getMessage(), $e->getCode(),[
                'line' => $e->getLine(),
                'file' => $e->getFile(),
            ]);
        }
    }

    /**
     * 列表
     * @return bool|string
     */
    public function list(): bool|string
    {
        try {
            $param = $this->request->param();
            $where = [];
            if (!empty($param['start_time']) && !empty($param['end_time'])) {
                $where[] = ['time', 'between', [$param['start_time'], $param['end_time']]];
            }else{
                if (!empty($param['start_time'])) {
                    $where[] = ['time', '>=', $param['start_time']];
                }
                if (!empty($param['end_time'])) {
                    $where[] = ['time', '<=', $param['end_time']];
                }
            }
            if(!empty($param['name'])){
                $where[] = ['name','like','%'.$param['name'].'%'];
            }
            if(!empty($param['uid'])){
                $where[] = ['uid','=',$param['uid']];
            }
            $where[] = ['unit_id', '=', $this->userData['unit_id']];
            $query = $this->mode->where($where);
            $data = $query->with(['user'=>function($query){
                $query->with(['roleName']);
            }])->order('id desc')->paginate($param['pageSize'])->toArray();
            if (count($data['data']) > 0) {
                foreach ($data['data'] as &$item) {
                    $item['size'] = $item['size'].'MB';
                    $item['role_name'] = $item['role_name']??'超级管理员';
                }
            }
            $data['ids'] = $query->column('id');
            return success($data);
        } catch (\Exception $e) {
            return error($e->getMessage(), $e->getCode(),[
                'line' => $e->getLine(),
                'file' => $e->getFile(),
            ]);
        }
    }

    /**
     * 备份人选择器
     * @return bool|string
     */
    public function BackupsSelector(): bool|string
    {
        try {
            $userMode = new UserModel();
            $where = [];
            $where[] = ['unit_id', '=', $this->userData['unit_id']];
            $ids = $this->mode->where($where)->group('uid')->column('uid');
            $list = $userMode->where('id','in',$ids)->field('id,name')->select();
            return success($list);
        }catch (\Exception $e) {
            return error($e->getMessage(), $e->getCode(),[
                'line' => $e->getLine(),
                'file' => $e->getFile(),
            ]);
        }
    }
    /**
     * 解析SQL文件为SQL语句数组
     * @param string $path
     * @return array|mixed|string
     */
    private function parseSQL($path = '')
    {
        $sql = file_get_contents($path);
        $sql = explode("\r\n", $sql);

        //先消除--注释
        $sql = array_filter($sql, function ($data) {
            if (empty($data) || preg_match('/^--.*/', $data)) {
                return false;
            } else {
                return true;
            }
        });
        $sql = implode('', $sql);
        //删除/**/注释
        $sql = preg_replace('/\/\*.*\*\//', '', $sql);
        return $sql;
    }


    /**
     *  获取还原备份的数据
     * @return false|string|Json|void
     */
    public function getRestoreBackups()
    {
        try {
            $id = $this->request->param('id');
            $path = $this->mode->where('id',$id)->field('name,path')->find();
            $path = '.'.$path['path'];
            if (!file_exists($path)) throw new \Exception('文件不存在！');
            // 读取文件内容
            $path = iconv('utf-8', 'gb2312', $path);
            $sqlContent = file_get_contents($path);
            $sqlContent = $this->parseSQL($path);
            // 将 SQL 内容按分号分割为多个语句
            $sqlStatements = explode(';', $sqlContent);
//            preg_match('/`([^`]+)`/', $subArray, $matches);
            $result = [];
            $insertIntoStatements = []; // 用于存放包含'INSERT INTO'的语句
            foreach ($sqlStatements as $item) {
                preg_match('/`([^`]+)`/', $item, $matches);
                if (isset($matches[1])) {
                    $key = $matches[1];
                    // 检查当前 SQL 语句是否包含 'INSERT INTO'
                    if (strpos($item, 'INSERT INTO') !== false) {
                        // 如果包含，存入单独的数组
                        $insertIntoStatements[] = trim($item);
                    } else {
                        // 否则，加入原始结果数组
                        if (!isset($result[$key])) {
                            $result[$key] = [];
                        }
                        $result[$key][] = trim($item);
                    }
                }
            }
            /*SET FOREIGN_KEY_CHECKS=0; 是一种用于在 MySQL 数据库中临时禁用外键约束的 SQL 命令。这通常在进行大量数据插入或删除操作时使用，以避免因外键约束而导致的错误*/
            $array = 'SET FOREIGN_KEY_CHECKS=0';
            array_unshift($result['jkyl_backups'],$array);
            // 创建一个新的数组来存放合并后的字符串
            $mergedArray = [];
            // 遍历原始数组，将每个子数组合并为字符串
            foreach ($result as $key => $subArray) {
                // 如果不超过10条，直接合并
                $mergedArray[$key] = implode(";", $subArray) . ';';
            }
            $insert = [];
            $tempArray = ''; // 临时数组用于存放当前组的元素
            foreach ($insertIntoStatements as $key => $subArray) {
                $tempArray .= $subArray. ';'; // 将当前 SQL 语句添加到临时数组
                // 每当临时数组达到5个元素时，将其添加到 $insert 数组
                if (strlen($tempArray) > 200) {
                    $insert[] = $tempArray;
                    $tempArray = ''; // 清空临时数组以便下次使用
                }
            }
            if (strlen($tempArray) > 0){
                $insert[] = $tempArray;
            }
            $list['content'] = $mergedArray;//删除表。创建表的sql语句
//            $list['content']['jkyl_backups'] = $mergedArray['jkyl_backups'];//删除表。创建表的sql语句
            $list['insert'] = $insert;//所有的新增sql
//            $list['insert'] = array_slice($insert,0,1);//所有的新增sql
            return success($list, '操作成功！');
        } catch (\Exception $e) {
            return error($e->getMessage(), $e->getCode(),[
                'line' => $e->getLine(),
                'file' => $e->getFile(),
            ]);
        }

    }

    /**
     * 还原备份的数据
     * @return bool|string|Json
     */
    public function restoreBackups2(): Json|bool|string
    {
        try {
//            $sql = $this->request->param('sql');
//            return $this->backup->restore($sql);
            /* 这里是后台同步，前端直接调用这一个接口就可以*/
            $id = $this->request->param('id');
            $path = $this->mode->where('id',$id)->field('name,path')->find();
            $lastSlashPosition = strrpos($path['path'], '/');
            if ($lastSlashPosition !== false) {
                // 从开始到最后一个斜杠的位置截取字符串
                $trimmedPath = substr($path['path'],0, $lastSlashPosition);
            } else {
                // 如果没有斜杠，返回原始路径
                $trimmedPath = $path;
            }
            return $this->backup->restore($path['name'],$this->rootPath.$trimmedPath);
        } catch (\Exception $e) {
            return error($e->getMessage(), $e->getCode(),[
                'line' => $e->getLine(),
                'file' => $e->getFile(),
            ]);
        }
    }
    public function restoreBackups(): Json|bool|string
    {
        try {
            $param = $this->request->param();
            $this->validate($param,[
                'id|ID' => 'require'
            ]);
            $info = $this->mode->find($param['id']);
            if (!$info) throw new \Exception('参数错误！');
            $backupService = new MysqlBackup();
            $result = $backupService->restore($info['path']);
            if (!$result['status']) throw new \Exception($result['msg']);
            return success();
        } catch (\Exception $e) {
            return error($e->getMessage(), $e->getCode(),[
                'line' => $e->getLine(),
                'file' => $e->getFile(),
            ]);
        }
    }
}